﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace Pku.BookStore.DAL
{
    public class UserDAL : BaseDAL
    {
        /// <summary>
        /// 增加用户
        /// </summary>
        /// <param name="entity"></param>
        public int Add(Model.UserModel entity)
        {
            string commandText = @"INSERT INTO [User](LevelID,UserName,UserPwd,TrueName,Email,Phone,Mobile,Address,PostCode,Question,Answer,RegTime,LastLoginTime,LastIP,Province,City,Area,Gender,IsLock)
VALUES(@LevelID,@UserName,@UserPwd,@TrueName,@Email,@Phone,@Mobile,@Address,@PostCode,@Question,@Answer,getdate(),getdate(),@LastIP,@Province,@City,@Area,@Gender,@IsLock);
SET  @UserID=scope_identity();
";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddOutParameter(command, "UserID", DbType.Int32, 10);
            DbMain.AddInParameter(command, "LevelID", DbType.Int16, entity.LevelID);
            DbMain.AddInParameter(command, "UserName", DbType.String, entity.UserName);
            DbMain.AddInParameter(command, "UserPwd", DbType.String, entity.UserPwd);
            DbMain.AddInParameter(command, "TrueName", DbType.String, entity.TrueName);
            DbMain.AddInParameter(command, "Email", DbType.AnsiString, entity.Email);
            DbMain.AddInParameter(command, "Phone", DbType.AnsiString, entity.Phone);
            DbMain.AddInParameter(command, "Mobile", DbType.AnsiString, entity.Mobile);
            DbMain.AddInParameter(command, "Address", DbType.String, entity.Address);
            DbMain.AddInParameter(command, "PostCode", DbType.String, entity.PostCode);
            DbMain.AddInParameter(command, "Question", DbType.String, entity.Question);
            DbMain.AddInParameter(command, "Answer", DbType.String, entity.Answer);
            DbMain.AddInParameter(command, "LastIP", DbType.AnsiString, entity.LastIP);
            DbMain.AddInParameter(command, "Province", DbType.String, entity.Province);
            DbMain.AddInParameter(command, "City", DbType.String, entity.City);
            DbMain.AddInParameter(command, "Area", DbType.String, entity.Area);
            DbMain.AddInParameter(command, "Gender", DbType.Int16, entity.Gender);
            DbMain.AddInParameter(command, "IsLock", DbType.Int16, entity.IsLock);

            DbMain.ExecuteNonQuery(command);

            int UserID = (int)DbMain.GetParameterValue(command, "UserID");
            return UserID;
        }

        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="entity"></param>
        public bool Modify(Model.UserModel entity)
        {
            string commandText = @"UPDATE [User] SET LevelID=@LevelID,TrueName=@TrueName,Email=@Email,Phone=@Phone,Mobile=@Mobile,Address=@Address,PostCode=@PostCode,Province=@Province,City=@City,Area=@Area,Gender=@Gender,IsLock=@IsLock WHERE UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);
            DbMain.AddInParameter(command, "LevelID", DbType.Int16, entity.LevelID);
            DbMain.AddInParameter(command, "TrueName", DbType.String, entity.TrueName);
            DbMain.AddInParameter(command, "Email", DbType.AnsiString, entity.Email);
            DbMain.AddInParameter(command, "Phone", DbType.AnsiString, entity.Phone);
            DbMain.AddInParameter(command, "Mobile", DbType.AnsiString, entity.Mobile);
            DbMain.AddInParameter(command, "Address", DbType.String, entity.Address);
            DbMain.AddInParameter(command, "PostCode", DbType.String, entity.PostCode);
            DbMain.AddInParameter(command, "Province", DbType.String, entity.Province);
            DbMain.AddInParameter(command, "City", DbType.String, entity.City);
            DbMain.AddInParameter(command, "Area", DbType.String, entity.Area);
            DbMain.AddInParameter(command, "IsLock", DbType.Int16, entity.IsLock);
            DbMain.AddInParameter(command, "Gender", DbType.Int16, entity.Gender);

            return DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="entity"></param>
        public bool ModifyPass(Model.UserModel entity)
        {
            string commandText = @"UPDATE [User] SET UserPwd=@UserPwd WHERE UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);
            DbMain.AddInParameter(command, "UserPwd", DbType.String, entity.UserPwd);

            return DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 修改用户安全问题
        /// </summary>
        /// <param name="entity"></param>
        public bool ModifySafe(Model.UserModel entity)
        {
            string commandText = @"UPDATE [User] SET Question=@Question,Answer=@Answer WHERE UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);
            DbMain.AddInParameter(command, "Question", DbType.String, entity.Question);
            DbMain.AddInParameter(command, "Answer", DbType.String, entity.Answer);

            return DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 修改用户最后登录IP和时间
        /// </summary>
        /// <param name="entity"></param>
        public bool ModifyLogin(Model.UserModel entity)
        {
            string commandText = @"UPDATE [User] SET LastLoginTime=@LastLoginTime,LastIP=@LastIP WHERE UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);
            DbMain.AddInParameter(command, "LastLoginTime", DbType.DateTime, entity.LastLoginTime);
            DbMain.AddInParameter(command, "LastIP", DbType.String, entity.LastIP);

            return DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 搜索用户
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<Model.UserModel> Search(Model.UserParam param)
        {
            var list = new List<Model.UserModel>();
            DbCommand dbCommand = DbMain.GetStoredProcCommand("UserPageSearch");
            dbCommand.CommandTimeout = 600;
            DbMain.AddInParameter(dbCommand, "DateStart", DbType.String, param.BeginDate.ToString("yyyy-MM-dd 00:00:00"));
            DbMain.AddInParameter(dbCommand, "DateEnd", DbType.String, param.EndDate.ToString("yyyy-MM-dd 23:59:59"));
            DbMain.AddInParameter(dbCommand, "UserName", DbType.String, param.UserName);
            DbMain.AddInParameter(dbCommand, "Page", DbType.Int32, param.PageNo);
            DbMain.AddInParameter(dbCommand, "Rows", DbType.Int32, param.PageRow);
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    list.Add(ConvertReaderToEntity(dr));
                }
            }
            return list;
        }

        /// <summary>
        /// 获取用户总数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Model.UserParam param)
        {
            string commandText = "SELECT COUNT(1) FROM [User] u(NOLOCK) WHERE 1=1 ";
            if (param != null)
            {
                if (param.BeginDate != DateTime.MinValue)
                {
                    commandText += " AND u.RegTime>=@BeginDate ";
                }
                if (param.EndDate != DateTime.MaxValue)
                {
                    commandText += " AND u.RegTime>=@EndDate ";
                }
                if (!string.IsNullOrEmpty(param.UserName))
                {
                    commandText += " AND u.UserName LIKE '%@UserName%'";
                }
            }
            var command = DbMain.GetSqlStringCommand(commandText);
            if (param != null)
            {
                DbMain.AddInParameter(command, "BeginDate", DbType.String, param.BeginDate);
                DbMain.AddInParameter(command, "EndDate", DbType.String, param.EndDate);
                DbMain.AddInParameter(command, "UserName", DbType.String, param.UserName);
            }
            return (int)DbMain.ExecuteScalar(command);
        }

        /// <summary>
        /// 返回用户信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public Model.UserModel GetEntityById(int userID)
        {
            string commandText = @"SELECT UserID,LevelID,UserName,UserPwd,TrueName,Email,Phone,Mobile,Address,PostCode,Question,Answer,RegTime,LastLoginTime,LastIP,Province,City,Area,Gender,IsLock  FROM  [User] WHERE UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, userID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    return ConvertReaderToEntity(dr);
                }
            }
            return null;
        }

        /// <summary>
        /// 实体转换
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static Model.UserModel ConvertReaderToEntity(IDataRecord dr)
        {
            return new Model.UserModel
            {
                UserID = (int)dr["UserID"],
                LevelID = (short)dr["LevelID"],
                UserName = dr["UserName"].ToString(),
                UserPwd = dr["UserPwd"].ToString(),
                TrueName = dr["TrueName"].ToString(),
                Email = dr["Email"].ToString(),
                Phone = dr["Phone"].ToString(),
                Mobile = dr["Mobile"].ToString(),
                Address = dr["Address"].ToString(),
                PostCode = dr["PostCode"].ToString(),
                Question = dr["Question"].ToString(),
                Answer = dr["Answer"].ToString(),
                RegTime = Convert.ToDateTime(dr["RegTime"]),
                LastLoginTime = Convert.ToDateTime(dr["LastLoginTime"]),
                LastIP = dr["LastIP"].ToString(),
                Province = dr["Province"].ToString(),
                City = dr["City"].ToString(),
                Area = dr["Area"].ToString(),
                Gender = (short)dr["Gender"],
                IsLock = (short)dr["IsLock"]
            };
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Str">关键字</param>
        /// <param name="Type">0为用户名  1为邮箱</param>
        /// <returns></returns>
        public bool Exist(string Str, int Type)
        {
            StringBuilder commandText = new StringBuilder();
            commandText.Append("SELECT COUNT(1) FROM [User] u WHERE 1=1");
            if (Type == 0)
            {
                commandText.Append(" AND u.UserName=@Str");
            }
            else if (Type == 1)
            {
                commandText.Append(" AND u.Email=@Str");
            }
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "Str", DbType.String, Str);
            return (int)DbMain.ExecuteScalar(command) > 0;
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public Model.UserModel Login(Model.UserModel entity)
        {
            string commandText = "SELECT *  FROM [User] u WHERE u.UserName=@UserName AND u.UserPwd=@UserPwd";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "UserName", DbType.String, entity.UserName);
            DbMain.AddInParameter(command, "UserPwd", DbType.String, entity.UserPwd);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    return ConvertReaderToEntity(dr);
                }
            }
            return null;
        }
    }

}